<?php
/**
 * 
 * @package qBase
 * @subpackage sql
 */


/**
 * The QMySqlConnection object provides an abstract layer to 
 * the MySql dataabse server
 * @package qBase
 * @subpackage sql
 */
class QMySqlConnection  extends QSqlConnection  
{
	/**
	 * The default MySQL port
	 *
	 */
	const DefaultPort = 3306;
	
	/**
	 * A list of QSqlDatabase that is populated only on demand
	 *
	 * @var array Array of QSqlDatabase
	 */
	private $databases;
	/**
	 * The list of charsets accepted by the engine as strings
	 *
	 * @var array
	 */
	private $accepted_charsets;	
	/**
	 * The currently selected database
	 *
	 * @var string
	 */
	private $selected_db;
	/**
	 * Constructs a QSqlConnection object
	 *
	 * @param string $host
	 * @param integer $port
	 * @param string $user
	 * @param string $pass
	 * @param string $db
	 */
	public function __construct($host, $port, $user, $pass, $db = null)
	{
		parent::__construct($host, $port, $user, $pass, $db);
		
		$this->escape_char = "`";
	}
	
	/**
	 * Connect to the database
	 *
	 */
	public function connect()
	{
		$this->reference = mysql_connect($this->host . ":" . $this->port, $this->user, $this->pass);
		if (mysql_errno($this->reference) != 0)
			throw new Exception(mysql_error($this->reference));
			
		if ($this->db)
			$this->selectDb($this->db);
	}
	
	/**
	 * Closes the connection to the database
	 *
	 */
	public function close()
	{
		mysql_close($this->reference);
		if (mysql_errno($this->reference) != 0)
			throw new Exception(mysql_error($this->reference));
	}
	
	// public $total = 0;
	
	/**
	 * Executes a query
	 *
	 * @param mixed $query Can be a string or a QSqlQuery
	 * @return returns a link to the result set
	 */
	public function query($query)
	{
		if ($query instanceof QSqlQuery)
			$query = $this->parseSqlQuery($query);

		// $st = microtime(true);
		$result = mysql_query($query, $this->reference);
		// $time = microtime(true) - $st;
		// $this->total += $time;
		// $this->debug($time." ({$this->total}) : ".$query);
		
		if (mysql_errno($this->reference) != 0)
		{
			var_dump($query);
			throw new Exception(mysql_error($this->reference)."\nQuery:\n{$query}\n\n");
		}
		return $result;
	}
	
	/**
	 * Executes a query on the specified database
	 *
	 * @param string $query
	 * @param string $db
	 * @return returns a link to the result set
	 */
	public function queryDb($query, $db)
	{
		$result = mysql_db_query($db, $query, $this->reference);
		if (mysql_errno($this->reference) != 0)
			throw new Exception(mysql_error($this->reference));
		return $result;
	}
	
	/**
	 * Executes an insert query and reurns the id
	 *
	 * @param string $query
	 * @return mixed the id of the inserted record
	 */
	public function insert($query)
	{
		$this->query($query);
		return mysql_insert_id($this->reference);
	}
	
	/**
	 * Gets the ID of the last inserted value. Only works on auto increments.
	 *
	 * @return integer
	 */
	public function getInsertId()
	{
		return mysql_insert_id($this->reference);
	}
	
	/**
	 * Fetches the row from a query result
	 *
	 * @param mixed $result
	 * @return array Associative array (column => value)
	 */
	public function fetchRow($result)
	{
		return mysql_fetch_assoc($result);
	}
	
	/**
	 * Fetches the value from the next row using the specified key
	 *
	 * @param mixed $result
	 * @param mixed $key
	 * @return mixed
	 */
	public function fetchValue($result, $key)
	{
		$row = mysql_fetch_assoc($result);
		if (isset($row[$key]))
			return $row[$key];
		else 
			return null;
	}
	
	/**
	 * Fetches the row from a query result as an array with numeric keys
	 *
	 * @param array $result Associative array (index => value)
	 */
	public function fetchArray($result)
	{
		return mysql_fetch_array($result);
	}
	
	/**
	 * Fetches the row from a query result as an object
	 *
	 * @param object $result Object
	 */
	public function fetchObject($result)
	{
		return mysql_fetch_object($result);
	}
	
	/**
	 * Counts the number of records resuled on a SELECT query
	 *
	 * @param mixed $result
	 * @return integer
	 */
	public function countRows($result)
	{
		return mysql_numrows($result);
	}
	
	/**
	 * Sets the current record in a recordset
	 *
	 * @param mixed $result
	 * @param integer $position
	 */
	public function setCurrentRow($result, $position)
	{
		mysql_data_seek($result, $position);
	}

	/**
	 * Gets the default charset for this connection
	 * @return string 
	 */
	public function getClientEncoding()
	{
		return mysql_client_encoding($this->reference);
	}
	
	/**
	 * Sets the connection encoding
	 *
	 * @param string $encoding
	 */
	public function setClientEncoding($encoding)
	{
		$this->query("SET NAMES '$encoding'");
	}
	
	/**
	 * Escapes a string for this connection
	 *
	 * @param string $str
	 * @return string
	 */
	public function escapeString($str)
	{
		return mysql_real_escape_string($str, $this->reference);
	}
	
	/**
	 * Escapes the name of a database, table, column, alias and so on
	 *
	 * @param string $str
	 */
	public function escapeName($str)
	{
		$str = str_replace($this->getEscapeChar(), "", $str);
		$str = str_replace("\"", "", $str);
		$str = str_replace(".", $this->getEscapeChar().".".$this->getEscapeChar(), $str);
		return "{$this->getEscapeChar()}{$str}{$this->getEscapeChar()}";
	}
	
	/**
	 * Sets a database as the default for this connection
	 *
	 * @param string $db
	 */
	public function selectDb($db)
	{
		if ($this->selected_db != $db)
		{
			mysql_select_db($db, $this->reference);
			if (mysql_errno($this->reference) != 0)
				throw new Exception(mysql_error($this->reference));
			$this->selected_db = $db;
		}
	}
	
	/**
	 * Gets the list of all databases available for this connection. Returns an array of QSqlDatabase(s).
	 * If refresh is set to true the list will be read again
	 * 
	 * @param boolean $refresh
	 * @return array
	 */
	public function getDatabases($refresh = false)
	{
		if ($this->databases && (!$refresh))
			return $this->databases;

		$this->databases = array();
		
		$result = mysql_list_dbs($this->reference);
		while ($row = mysql_fetch_assoc($result))
			$this->databases[] = $this->getDatabaseInfo($row["Database"]);
		
		return $this->databases;
	}
	
	/**
	 * Gets a QSqlDatabase object from the name of the database
	 *
	 * @param string $database
	 * @return QSqlDatabase
	 */
	public function getDatabaseInfo($database)
	{
		$this->selectDb($database);

		$charset = $this->queryOneValue("SHOW VARIABLES LIKE 'character_set_database'", "Value");
		$collation = $this->queryOneValue("SHOW VARIABLES LIKE 'collation_database'", "Value");

		return new QSqlDatabase($this, $database, $charset, $collation);
	}
	
	/**
	 * Creates the specified database
	 *
	 * @param QSqlDatabase $db
	 */
	public function createDatabase(QSqlDatabase $db)
	{
		/*
		CREATE DATABASE db_name DEFAULT CHARACTER SET = charset_name DEFAULT COLLATE = collation_name
		CREATE DATABASE `aaaaaaaaaaaaaa` DEFAULT CHARACTER SET utf8 COLLATE utf8_persian_ci;
		*/
		
		$query = "CREATE DATABASE ".$this->escapeName($db);
		if ($db->getCharset())
			$query .= " DEFAULT CHARACTER SET = '".$this->escapeString($db->getCharset())."'";
		if ($db->getCollation())
			$query .= " DEFAULT COLLATE = '".$this->escapeString($db->getCollation())."'";
		
		$this->query($query);
	}

	/**
	 * Tests if the specified database name exists for the current connection.
	 *
	 * @param string $db_name
	 * @return boolean
	 */
	public function hasDatabase($db_name)
	{
		$the_db = $this->queryOneValue("SHOW databases like '{$this->escapeString($db_name)}'", 0);
		
		if ($the_db)
			return true;
		else 
			return false;
	}
	
	/**
	 * Determines if the specified database contains the table
	 *
	 * @param string $database_name
	 * @param string $table_name
	 * @return boolean
	 */
	public function hasTable($database_name, $table_name)
	{
		$the_table = $this->queryOneValue("SHOW TABLES IN {$this->escapeName($database_name)} LIKE '{$this->escapeString($table_name)}'", 0);
		if ($the_table)
			return true;
		else 
			return false;
	}
	
	/**
	 * Gets an array of strings containing the names of the tables in the database
	 *
	 * @param string $database_name
	 * @return array Array of strings
	 */
	public function getTablesInDatabase($database_name)
	{
		$result = $this->query("SHOW TABLES IN {$this->escapeName($database_name)}");
		$tables = array();
		while ($row = $this->fetchArray($result))
			$tables[] = $row[0];
			
		return $tables;
	}
	
	/**
	 * Renames the database
	 *
	 * @param string $db_name
	 * @param string $new_name
	 */
	public function renameDatabase($db_name, $new_name)
	{
		// RENAME {DATABASE | SCHEMA} db_name TO new_db_name;
		$this->query("RENAME {$this->escapeName($db_name)} TO {$this->escapeName($new_name)}");
	}
	
	/**
	 * Sets the database's collation. It's safe to change the charset before using a collation of a different set.
	 *
	 * @param string $db_name
	 * @param string $collation
	 */
	public function setDatabaseCollation($db_name, $collation)
	{
		// ALTER DATABASE `qbase_test` DEFAULT CHARACTER SET utf8 COLLATE utf8_bin
		$this->query("ALTER DATABASE {$this->escapeName($db_name)} COLLATE '{$this->escapeString($collation)}'");
	}
	
	/**
	 * Sets the database charset
	 *
	 * @param string $db_name
	 * @param string $charset
	 */
	public function setDatabaseCharset($db_name, $charset)
	{
		// ALTER DATABASE `qbase_test` DEFAULT CHARACTER SET utf8 COLLATE utf8_bin
		$this->query("ALTER DATABASE {$this->escapeName($db_name)} DEFAULT CHARACTER SET '{$this->escapeString($charset)}'");
	}
	
	/**
	 * Creates a table based on the QSqlTable structure
	 *
	 * @param string $database
	 * @param QSqlTable $table
	 * @param boolean $drop_if_exists
	 */
	public function createTable($database, QSqlTable $table, $drop_if_exists = false)
	{
		/*
		CREATE TABLE `qbase_test`.`blabla` (
			`sadfsad` INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY COMMENT 'comm 1',
			`faf` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'comm 2',
			`qwer` ENUM( 'y', 'n' ) NOT NULL DEFAULT 'n' COMMENT 'comm 3',
			`qwerqwe` TEXT CHARACTER SET utf8 COLLATE utf8_romanian_ci NULL COMMENT 'comm 4'
			) ENGINE = InnoDB CHARACTER SET utf8 COLLATE utf8_romanian_ci COMMENT = 'table commeeeenntssss';
			
		*/
		
		if ($drop_if_exists)
			$this->query("DROP TABLE IF EXISTS {$this->escapeName($table->getName())};");
		
		$query = "CREATE TABLE ";
		$query .= $this->escapeName($database) . "." . $this->escapeName($table->getName());
		$query .= " ( ";
		
		$query_indexes = "";
		
		$pos = 0;
		$fields = $table->getFields();
		foreach ($fields as $field)
		{
			if ($pos > 0)
				$query .= ", ";

			$query .= $this->escapeName($field->getName());
			$query .= " ".$this->getFieldModelString($field);

			$pos++;
		}
		
		$query .= " ) ";
		if ($table->getEngine())
			$query .= " ENGINE = {$table->getEngine()}";

		if ($table->getCharset())
			$query .= " CHARACTER SET {$table->getCharset()}";
		if ($table->getCollation())
			$query .= " COLLATE {$table->getCollation()}";
			
		if ($table->getComments())
			$query .= " COMMENT = '{$this->escapeString($table->getComments())}'";

		$this->query($query);
		unset($query);
			
		// ALTER TABLE `asdf` ADD INDEX ( `dfas` ) 
		$indexes = $table->getIndexes();
		if ($indexes)
		{
			foreach ($indexes as $index)
			{
				// skip for autoincrement column
				if (($index->getFieldsCount() == 1) && $index->getFieldAt(0)->getAutoIncrement())
					continue;
				
				$query = "ALTER TABLE {$this->escapeName($table->getName())} ADD ".$this->getIndexModelString($index);
				$this->query($query);
			}
		}
	}
	
	/**
	 * Gets the mySql string representation for the creation of an index
	 *
	 * @param QSqlTableIndex $index
	 * @return unknown
	 */
	private function getIndexModelString(QSqlTableIndex $index)
	{
		// indexes : UNIQUE ( `sdfasd` ) 
		// indexes : INDEX ( `asdfasdfas` )
		// indexes : FULLTEXT ( `asdfasdfas` )
		// indexes : PRIMARY KEY ( `id` , `rtywrety` ) ;
		
		$query = "";
		if ($index->getType() == QSqlTableIndex::IndexNormal)
			$query .= "INDEX";
		else if ($index->getType() == QSqlTableIndex::IndexUnique)
			$query .= "UNIQUE";
		else if ($index->getType() == QSqlTableIndex::IndexPrimary)
			$query .= "PRIMARY KEY";
		else if ($index->getType() == QSqlTableIndex::IndexFullText)
			$query .= "FULLTEXT";
		else 
			throw new Exception("Unknown index type : {$index->getType()}");
			
		// not for PK
		if ($index->getName() && ($index->getType() != QSqlTableIndex::IndexPrimary))
			$query .= " ".$this->escapeName($index->getName())." ";
			
		$query .= " ( ";
		
		$count = $index->countFields();

		for ($i = 0; $i < $count; $i++)
		{
			if ($i > 0)
				$query .= ", ";
			$field = $index->getFieldAt($i);
			$query .= "`{$field->getName()}`";
		}

		$query .= " ) ";
		
		return $query;
	}
	
	/**
	 * Gets the string for a filed that can be used in CREATE TABLE, ALTER TABLE statements
	 *
	 * @param QSqlTableField $field
	 * @return string
	 */
	private function getFieldModelString(QSqlTableField $field)
	{
		/*
		`sadfsad` INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY COMMENT 'comm 1',
			`faf` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'comm 2',
			`qwer` ENUM( 'y', 'n' ) NOT NULL DEFAULT 'n' COMMENT 'comm 3',
			`qwerqwe` TEXT CHARACTER SET utf8 COLLATE utf8_romanian_ci NULL COMMENT 'comm 4'
			 `asjfasdf` VARCHAR( 34 ) CHARACTER SET ucs2 COLLATE ucs2_lithuanian_ci NULL DEFAULT NULL COMMENT 'sdgsdfgsd',
			  `vgfrwebvg` INT( 5 ) UNSIGNED NULL DEFAULT '23' COMMENT 'wertwert',
		*/
		$query = $this->encodeFieldType( $field->getType() );

		if ($field->getLength())
			$query .= "({$field->getLength()})";
		else if ($field->getValues())
			$query .= "({$field->getValues()})";
			
		if ($field->getUnsigned())
			$query .= " UNSIGNED";
			
		if ($field->getCharset())
			$query .= " CHARACTER SET {$field->getCharset()}";
		if ($field->getCollation())
			$query .= " COLLATE {$field->getCollation()}";
			
		if ($field->getNull())
			$query .= " NULL";
		else 
			$query .= " NOT NULL";
			
		$field_default = $field->getDefault();
		if (!is_null($field_default))
		{
			if ($field_default instanceof QSqlNull)
				$query .= " DEFAULT NULL";
			else 
				$query .= " DEFAULT '{$this->escapeString($field_default)}'";
		}
			
		// auto increment forces PK
		if ($field->getAutoIncrement())
			$query .= " AUTO_INCREMENT PRIMARY KEY ";
			
		if ($field->getComments())
			$query .= " COMMENT '".$this->escapeString($field->getComments())."'";
			
		/* QSqlTable $table, $name, $type, $length = null, $values = null, $default = null, $charset = null, 
		$collation = null, $unsigned = null, $null = null, $auto_increment = null, $comments = null*/
			
		return $query;
	}
	
	/**
	 * Casts the field type to string representing the MySQL data type
	 *
	 * @param integer $type
	 * @return string
	 */
	private function encodeFieldType($type)
	{
		switch ($type)
		{
			case QSqlTableField::TypeInt:
				{
					return "INT";
				}
			case QSqlTableField::TypeSmallint:
				{
					return "SMALLINT";
				}
			case QSqlTableField::TypeMediumint:
			{
				return "MEDIUMINT";
			}
			case QSqlTableField::TypeTinyint:
				{
					return "TINYINT";
				}
			case QSqlTableField::TypeBigint:
				{
					return "BIGINT";
				}
			case QSqlTableField::TypeDecimal:
				{
					return "DECIMAL";
				}
			case QSqlTableField::TypeFloat:
				{
					return "FLOAT";
				}
			case QSqlTableField::TypeDouble:
				{
					return "DOUBLE";
				}
			case QSqlTableField::TypeBit:
				{
					return "BIT";
				}
			case QSqlTableField::TypeBool:
				{
					return "TINYINT";
				}
			case QSqlTableField::TypeChar:
				{
					return "CHAR";
				}
			case QSqlTableField::TypeVarchar:
				{
					return "VARCHAR";
				}
			case QSqlTableField::TypeText:
				{
					return "TEXT";
				}
			case QSqlTableField::TypeMediumText:
				{
					return "MEDIUMTEXT";
				}
			case QSqlTableField::TypeLongText:
				{
					return "LONGTEXT";
				}
			case QSqlTableField::TypeBlob:
				{
					return "BLOB";
				}
			case QSqlTableField::TypeEnum:
				{
					return "ENUM";
				}	
			case QSqlTableField::TypeSet:
				{
					return "SET";
				}
			case QSqlTableField::TypeDate:
				{
					return "DATE";
				}
			case QSqlTableField::TypeDatetime:
				{
					return "DATETIME";
				}
			case QSqlTableField::TypeTimestamp:
				{
					return "TIMESTAMP";
				}
			case QSqlTableField::TypeTime:
				{
					return "TIME";
				}
			default:
				{
					throw new Exception("Unknown field type: {$type}");
				}
		}
	}
	
	/**
	 * Converts the string field type (ex: VARCHAR) to the defined constant (ex: QSqlTableField::TypeVarchar)
	 *
	 * @param string $type
	 * @return integer
	 */
	private function decodeFieldType($type, $filed_length = null)
	{
		$type = strtoupper(trim($type));
		switch ($type)
		{
			case "INT":
			{
				return QSqlTableField::TypeInt;
			}
			case "TINYINT":
			{
				if ((!is_null($filed_length)) && ($filed_length == 1))
					return QSqlTableField::TypeBool;
				else 
					return QSqlTableField::TypeTinyint;
			}
			case "SMALLINT":
			{
				return QSqlTableField::TypeSmallint;
			}
			case "MEDIUMINT":
			{
				return QSqlTableField::TypeMediumint;
			}
			case "BIGINT":
			{
				return QSqlTableField::TypeBigint;
			}
			case "DECIMAL":
			{
				return QSqlTableField::TypeDecimal;
			}
			case "FLOAT":
			{
				return QSqlTableField::TypeFloat;
			}
			case "DOUBLE":
			{
				return QSqlTableField::TypeDouble;
			}
			case "BIT":
			{
				return QSqlTableField::TypeBit;
			}
			case "BOOL":
			{
				return QSqlTableField::TypeBool;
			}
			case "CHAR":
			{
				return QSqlTableField::TypeChar;
			}
			case "VARCHAR":
			{
				return QSqlTableField::TypeVarchar;
			}
			case "TEXT":
			{
				return QSqlTableField::TypeText;
			}
			case "MEDIUMTEXT":
			{
				return QSqlTableField::TypeMediumText;
			}
			case "LONGTEXT":
			{
				return QSqlTableField::TypeLongText;
			}
			case "BLOB":
			{
				return QSqlTableField::TypeBlob;
			}
			case "ENUM":
			{
				return QSqlTableField::TypeEnum;
			}
			case "SET":
			{
				return QSqlTableField::TypeSet;
			}
			case "DATE":
			{
				return QSqlTableField::TypeDate;
			}
			case "DATETIME":
			{
				return QSqlTableField::TypeDatetime;
			}
			case "TIMESTAMP":
			{
				return QSqlTableField::TypeTimestamp;
			}
			case "TIME":
			{
				return QSqlTableField::TypeTime;
			}
			default:
			{
				throw new Exception("Unknown field type: {$type}");
			}
		}
	}
	
	/**
	 * Gets the full table information including indexes and fields, collation, charset, engine, etc ...
	 *
	 * @param string $database_name
	 * @param string $table_name
	 * @return QSqlTable
	 */
	public function getTableInfo($database_name, $table_name)
	{
		$result = $this->query("SHOW FULL COLUMNS FROM {$this->escapeName($database_name)}.{$this->escapeName($table_name)}");
		
		$fields = array();
		// Field 	Type 	Collation 	Null 	Key 	Default 	Extra 	Privileges 	Comment 
		// id  	int(10) unsigned  	NULL 	NO 	PRI  	NULL 	auto_increment 	select,insert,update,references 	the pk
		// name 	varchar(255) 	utf8_romanian_ci 	NO 	UNI 	  	  	select,insert,update,references 	the name field
		// order 	int(10) unsigned 	NULL 	NO 	  	0 	  	select,insert,update,references 	horder man
		// active 	enum('Yes','No') 	utf8_romanian_ci 	NO 	  	Yes 	  	select,insert,update,references 	aactiveee man

		while ($row = $this->fetchRow($result))
		{
			$f_name = $row["Field"];
			
			// type / length / values / unsigned
			list($f_type, $f_length, $f_values, $f_unsigned) = $this->parseFieldType($row["Type"]);
			
			$f_type = $this->decodeFieldType($f_type, $f_length);
			
			$f_charset = $this->getCharsetFromCollation($row["Collation"]);
			$f_collation = $row["Collation"];
			
			$f_null = ($row["Null"] == "YES");
			// mySQL will always SHOW null even if default is not null so make sure the column accepts null
			$f_default = (is_null($row["Default"]) && $f_null) ? QSqlNull::Get() : $row["Default"];
			$f_auto_increment = (trim($row["Extra"]) == "auto_increment");
			$f_comment = $row["Comment"];
			
			$fields[$f_name] = new QSqlTableField(null, $f_name, $f_type, $f_length, $f_values, $f_default, $f_charset, $f_collation, $f_unsigned, $f_null, $f_auto_increment, $f_comment);
		}

		$indexes = array();
		
		$result = $this->query("SHOW INDEX FROM {$this->escapeName($database_name)}.{$this->escapeName($table_name)}");
		
		while ($row = $this->fetchRow($result))
		{
			/**
				Table 		Non_unique 	Key_name 		Seq_in_index 	Column_name 	Collation 	Cardinality 	Sub_part 	Packed 	Null 	Index_type 	Comment
				test_1 		0 			PRIMARY 		1 				id 				A 			0 				NULL 		NULL 		  	BTREE 	 
				test_1 		0 			uniq_0 			1 				name 			A 			0 				NULL 		NULL 		  	BTREE 	 
				test_1 		1 			name 			1 				name 			A 			NULL 			NULL 		NULL 		  	BTREE 	 
				test_1 		1 			name 			2 				order 			A 			NULL 			NULL 		NULL 		  	BTREE 	 
				test_1 		1 			sometetx 		1 				sometetx 		NULL 		NULL 			NULL 		NULL 		  	FULLTEXT 	 
			 */
			$i_name = $row["Key_name"];
			
			if (isset($indexes[$i_name]))
				$current_index = $indexes[$i_name];
			else 
			{
				$current_index = new QSqlTableIndex();
				$indexes[$i_name] = $current_index;
			}
			
			$current_index->setName($i_name, false);
			
			// establish type
			if (strtoupper(trim($i_name)) == "PRIMARY")
				$current_index->setType(QSqlTableIndex::IndexPrimary);
			else 
			{
				if (strtoupper(trim($row["Index_type"])) == "FULLTEXT")
					$current_index->setType(QSqlTableIndex::IndexFullText);
				else if (trim($row["Non_unique"]) == "1")
					$current_index->setType(QSqlTableIndex::IndexNormal);
				else 
					$current_index->setType(QSqlTableIndex::IndexUnique);
			}
			
			$current_index->addField($fields[$row["Column_name"]]);
		}

		$row_info = $this->queryOneRow("SHOW TABLE STATUS IN {$this->escapeName($database_name)} LIKE '{$this->escapeString($table_name)}'");
		// "Engine" | "Collation" | "Comment"
		$engine = trim($row_info["Engine"]);
		$collation = trim($row_info["Collation"]);
		$charset = $this->getCharsetFromCollation($collation);
		$comments = trim($row_info["Comment"]);

		// fix fields (remove index), fix indexes
		$list_fields = array_values($fields);
		$list_indexes = array_values($indexes);

		$table = new QSqlTable($this->getDatabaseInfo($database_name), $table_name, $list_fields, $list_indexes, $engine, $charset, $collation, $comments);
		
		return $table;
	}
	
	/**
	 * Drops the table from the database
	 *
	 * @param string $database_name
	 * @param string $table_name
	 */
	public function dropTable($database_name, $table_name)
	{
		$this->query("DROP TABLE {$this->escapeName($database_name)}.{$this->escapeName($table_name)}");
	}
	
	/**
	 * Ads the specified fields to the table
	 *
	 * @param string $database_name
	 * @param string $table_name
	 * @param array $fields Array of QSqlTableField
	 */
	public function addTableFields($database_name, $table_name, $fields)
	{
		$query = "ALTER TABLE {$this->escapeName($database_name)}.{$this->escapeName($table_name)} ";
		
		$pos = 0;
		foreach ($fields as $field)
		{
			if ($pos > 0)
				$query .= ", ";
			$query .= " ADD COLUMN {$this->escapeName($field->getName())} ".$this->getFieldModelString($field);
			$pos++;
		}
		
		$query .= ";";

		$this->query($query);
	}
	
	/**
	 * Updates the specified table fields. If the fields do not exist an exception will be throwm
	 *
	 * @param string $database_name
	 * @param string $table_name
	 * @param array $fields Array of QSqlTableField
	 */
	public function updateTableFields($database_name, $table_name, $fields)
	{
		// ALTER TABLE `test_1` CHANGE `add_1` `add_1` INT( 10 ) UNSIGNED NOT NULL DEFAULT '1' COMMENT 'comm add_1'
		$query = "ALTER TABLE {$this->escapeName($database_name)}.{$this->escapeName($table_name)} ";
		
		$pos = 0;
		foreach ($fields as $field)
		{
			if ($pos > 0)
				$query .= ", ";
			$query .= " CHANGE COLUMN {$this->escapeName($field->getOldName())} {$this->escapeName($field->getName())} ".$this->getFieldModelString($field);
			$pos++;
		}
		
		$query .= ";";
		
		//var_dump($query);
		//return ;

		$this->query($query);
	}
	
	/**
	 * Removes the specified table fields. The fields are specified as an array of strings
	 *
	 * @param string $database_name
	 * @param string $table_name
	 * @param string $fields_names Array of string
	 */
	public function removeTableFields($database_name, $table_name, $fields_names)
	{
		$query = "ALTER TABLE {$this->escapeName($database_name)}.{$this->escapeName($table_name)} ";
		
		$pos = 0;
		foreach ($fields_names as $field)
		{
			if ($pos > 0)
				$query .= ", ";
			$query .= " DROP COLUMN {$this->escapeName($field)} ";
			$pos++;
		}
		
		$query .= ";";

		$this->query($query);
	}
	
	/**
	 * Ads an index to the table
	 *
	 * @param string $database_name
	 * @param string $table_name
	 * @param QSqlTableIndex $index
	 */
	public function addTableIndex($database_name, $table_name, QSqlTableIndex $index)
	{
		$query = "ALTER TABLE {$this->escapeName($database_name)}.{$this->escapeName($table_name)} ";
		$query .= " ADD ".$this->getIndexModelString($index);
		
		// var_dump($query);
		$this->query($query);
	}
	
	/**
	 * Updates an index
	 *
	 * @param string $database_name
	 * @param string $table_name
	 * @param QSqlTableIndex $index
	 */
	public function updateTableIndex($database_name, $table_name, QSqlTableIndex $index)
	{
		$query = "ALTER TABLE {$this->escapeName($database_name)}.{$this->escapeName($table_name)} ";
		$query .= " DROP INDEX ".$this->escapeName($index->getName()).", ";
		$query .= " ADD ".$this->getIndexModelString($index);
		
		// var_dump($query);
		$this->query($query);
	}
	
	/**
	 * Removes an index from the table
	 *
	 * @param string $database_name
	 * @param string $table_name
	 * @param string $index_name
	 */
	public function removeTableIndex($database_name, $table_name, $index_name)
	{
		// ALTER TABLE `qbase_test`.`test_1` DROP INDEX `tindex_1`
		$query = "ALTER TABLE {$this->escapeName($database_name)}.{$this->escapeName($table_name)} ";
		$query .= " DROP INDEX ".$this->escapeName($index_name);
		$this->query($query);
	}
	
	/**
	 * Renames a table
	 *
	 * @param string $database_name
	 * @param string $table_name
	 * @param string $new_name
	 */
	public function renameTable($database_name, $table_name, $new_name)
	{
		$query = "RENAME TABLE {$this->escapeName($database_name)}.{$this->escapeName($table_name)} TO ".
			"{$this->escapeName($database_name)}.{$this->escapeName($new_name)}";
			
		$this->query($query);
	}
	
	/**
	 * Sets the table comment
	 *
	 * @param string $database_name
	 * @param string $table_name
	 * @param string $comment
	 */
	public function setTableComment($database_name, $table_name, $comment)
	{
		$query = "ALTER TABLE {$this->escapeName($database_name)}.{$this->escapeName($table_name)}  COMMENT = '{$this->escapeString($comment)}'";

		$this->query($query);
	}
	
	/**
	 * Sets the table's engine
	 *
	 * @param string $database_name
	 * @param string $table_name
	 * @param string $engine
	 */
	public function setTableEngine($database_name, $table_name, $engine)
	{
		// ALTER TABLE `test_1`  ENGINE = InnoDB
		$query = "ALTER TABLE {$this->escapeName($database_name)}.{$this->escapeName($table_name)}  ENGINE = $engine";

		$this->query($query);
	}
	
	/**
	 * Sets the table charset
	 *
	 * @param string $database_name
	 * @param string $table_name
	 * @param string $charset
	 */
	public function setTableCharset($database_name, $table_name, $charset)
	{
		// ALTER TABLE `test_1`  DEFAULT CHARACTER SET swe7 COLLATE swe7_swedish_ci
		$query = "ALTER TABLE {$this->escapeName($database_name)}.{$this->escapeName($table_name)}  DEFAULT CHARACTER SET = $charset";

		$this->query($query);
	}
	
	/**
	 * Sets the table collation
	 *
	 * @param string $database_name
	 * @param string $table_name
	 * @param string $collation
	 */
	public function setTableCollation($database_name, $table_name, $collation)
	{
		// ALTER TABLE `test_1`  DEFAULT CHARACTER SET swe7 COLLATE swe7_swedish_ci
		$query = "ALTER TABLE {$this->escapeName($database_name)}.{$this->escapeName($table_name)} COLLATE = $collation";

		$this->query($query);
	}
	
	/**
	 * Parsese the field type that is obtain from a 'SHOW FULL COLUMNS FROM ...' command
	 * ex: ENUM('Yes','No')
	 * ex: int(10) unsigned
	 * ex: varchar(64)
	 * The result is an array : array(type, length, values, unsigned)
	 *
	 * @param string $field_type
	 * @return array
	 */
	private function parseFieldType($field_type)
	{
		$f_type = null;
		$f_length = null;
		$f_values = null;
		$f_unsigned = false;
		
		$end_type = strpos($field_type, "(");
		if ($end_type !== false)
		{
			$f_type = trim(substr($field_type, 0, $end_type));
			$end_length = strpos($field_type, ")", $end_type + 1);
			if ($end_length === false)
				throw new Exception("Invalid or unknown format");
				
			if ((strtoupper($f_type) == "ENUM") || (strtoupper($f_type) == "SET"))
			{
				$f_length = null;
				$f_values = substr($field_type, $end_type + 1, $end_length - $end_type - 1);
			}
			else 
			{
				$f_length = substr($field_type, $end_type + 1, $end_length - $end_type - 1);
				$f_values = null;
			}
			
			$parts = explode(" ", trim(substr($field_type, $end_length + 1)));
			$c_parts = count($parts);
			
			for ($i = 0; $i < $c_parts; $i++)
			{
				if (strtolower(trim($parts[$i])) == "unsigned")
				{
					$f_unsigned = true;
					break;
				}
			}
		}
		else 
		{
			$end_type = strpos($field_type, " ");
			if ($end_type !== false)
			{
				// $f_type = trim(substr($field_type, 0, $end_type));
				$parts = explode(" ", $field_type);
				$f_type = $parts[0];
				
				$c_parts = count($parts);

				for ($i = 1; $i < $c_parts; $i++)
					if (strtolower(trim($parts[$i])) == "unsigned")
					{
						$f_unsigned = true;
						break;
					}
			}
			else 
			{
				// only type is specified
				$f_type = trim($field_type);
			}
		}
		
		return array($f_type, $f_length, $f_values, $f_unsigned);
		// returns : type / length / values / unsigned
		// ex : int(10) unsigned
		//      varchar(255)
		//      int(10) unsigned
		//      enum('Yes','No')
		
	}
	
	/**
	 * Gets the charset from the collation
	 * Since the collation is a subset of a charset we can obtain the charset from it
	 * This is necessary as there is no other way to grab the charset for a table or column
	 *
	 * @param string $collation
	 * @return string
	 */
	private function getCharsetFromCollation($collation)
	{
		$collation = strtolower($collation);
		$pos = 0;
		$this->getAcceptedCharsets();
		
		while (($sep_pos = strpos($collation, "_", $pos)) !== false)
		{
			$charset = substr($collation, 0, $sep_pos);
			if (in_array($charset, $this->accepted_charsets))
				return $charset;
			
			$pos = $sep_pos + 1;
		}
		return null;
	}

	/**
	 * Gets the accepted charsets by the SQL engine
	 *
	 * @param boolean $refresh If true it forces a refresh, if not the value will be taken from the cache
	 * @return array Array of strings
	 */
	public function getAcceptedCharsets($refresh = false)
	{
		if ($this->accepted_charsets && (!$refresh))
			return $this->accepted_charsets;

		$this->accepted_charsets = array();
		$result = $this->query("SHOW CHARACTER SET");
		while ($row = $this->fetchRow($result))
			$this->accepted_charsets[] = strtolower($row["Charset"]);

		return $this->accepted_charsets;
	}
	
	/**
	 * Empties the table and resests all indexes and autoincrements. This should 
	 * be the equivalent of a table drop and recreation.
	 *
	 * @param string $database_name
	 * @param string $table_name
	 */
	public function truncateTable($database_name, $table_name)
	{
		$query = "TRUNCATE TABLE {$this->escapeName($database_name)}.{$this->escapeName($table_name)}";

		$this->query($query);
	}
	
	/**
	 * Transforms a QSqlQuery object into an SQL query string
	 *
	 * @param QSqlQuery $query
	 * @return string
	 */
	public function parseSqlQuery(QSqlQuery $query)
	{
		$str = "";
		
		$main_op = null;

		$last_op = null;
		$last_var = false;
		$last_data = false;
		
		foreach ($query->params as $param)
		{
			if ($param->type == QSqlQuery::KeyVariable)
			{
				if ($last_var || $last_data)
					$str .= ",";

				$str .= $this->escapeName($param->data);
				$last_var = true;
				$last_data = false;
			}
			else if ($param->type == QSqlQuery::KeyValue)
			{
				if ($last_var || $last_data)
					$str .= ",";

				if ($param->data instanceof QSqlNull)
					$str .= " NULL";
				else 
					$str .= " '{$this->escapeString($param->data)}'";
				$last_var = false;
				$last_data = true;
			}
			else if ($param->type == QSqlQuery::KeyReserved ) 
			{
				$last_var = false;
				$last_data = false;
				switch ($param->data)
				{
					case QSqlQuery::OpSelect:
						{
							$main_op = QSqlQuery::OpSelect;
							$str .= "SELECT ";
							break;
						}
					case QSqlQuery::OpAs:
						{
							$str .= " AS ";
							break;
						}
					case QSqlQuery::OpFrom:
						{
							$str .= " FROM ";
							break;
						}
					case QSqlQuery::OpJoin:
						{
							$str .= " JOIN ";
							break;
						}
					case QSqlQuery::OpOn:
						{
							$str .= " ON ";
							break;
						}
					case QSqlQuery::OpEquals:
						{
							$str .= " = ";
							break;
						}
					case QSqlQuery::OpWhere:
						{
							$str .= " WHERE ";
							break;
						}
					case QSqlQuery::OpLike:
						{
							$str .= " LIKE ";
							break;
						}
					case QSqlQuery::OpOr:
						{
							$str .= " OR ";
							break;
						}
					case QSqlQuery::OpAnd:
						{
							$str .= " AND ";
							break;
						}
					case QSqlQuery::OpLeftBrachet:
						{
							$str .= " ( ";
							break;
						}
					case QSqlQuery::OpRightBrachet:
						{
							$str .= " ) ";
							break;
						}
					case QSqlQuery::OpGreaterThem:
						{
							$str .= " > ";
							break;
						}
					case QSqlQuery::OpOrderBy:
						{
							$str .= " ORDER BY ";
							break;
						}
					case QSqlQuery::OpAsc:
						{
							$str .= " ASC ";
							break;
						}
					case QSqlQuery::OpDesc:
						{
							$str .= " DESC ";
							break;
						}
					case QSqlQuery::OpInsert:
						{
							$main_op = QSqlQuery::OpInsert;
							$str .= "INSERT INTO ";
							break;
						}
					case QSqlQuery::OpUpdate:
						{
							$main_op = QSqlQuery::OpUpdate;
							$str .= "UPDATE ";
							break;
						}
					case QSqlQuery::OpMerge:
						{
							$main_op = QSqlQuery::OpMerge;
							$str .= "MERGE ";
							break;
						}
					case QSqlQuery::OpDelete:
						{
							$main_op = QSqlQuery::OpDelete;
							$str .= "DELETE ";
							break;
						}
					case QSqlQuery::OpValues:
						{
							$str .= " VALUES ";
							break;
						}
					case QSqlQuery::OpSet:
						{
							$str .= " SET ";
							break;
						}
					default:
						{
							throw new Exception("Unknown reserved word or function : {$param->data}");
						}
				}
				$last_op = $param->data;
			}
			else 
			{
				throw new Exception("Unknow QSqlQueryParam type in the query : {$param->type}");
			}
		}
		return $str;
	}
	
	/*
	public function insert($database_name, $table_name, $values)
	{
		$pos = 0;
		$cols = "";
		$vals = "";
		foreach ($values as $key => $value)
		{
			if ($pos > 0)
			{
				$cols .= ", ";
				$vals .= ", ";
			}
			$cols .= $this->escapeName($key);
			$vals .= "'{$this->escapeString($value)}'";
			$pos++;
		}
		
		$query = "INSERT INTO {$this->escapeName($database_name)}.{$this->escapeName($table_name)} ($cols) VALUES ($vals);";
		$this->query($query);
	}
	
	public function update($database_name, $table_name, $values)
	{
		$query = "UPDATE {$this->escapeName($database_name)}.{$this->escapeName($table_name)} ($cols) VALUES ($vals);";
		$pos = 0;
		$cols = "";
		$vals = "";
		foreach ($values as $key => $value)
		{
			if ($pos > 0)
			{
				$cols .= ", ";
				$vals .= ", ";
			}
			$cols .= $this->escapeName($key);
			$vals .= "'{$this->escapeString($value)}'";
			$pos++;
		}
		
		
		$this->query($query);
	}
	*/
}

?>